package DataBase;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Types;
import java.text.DateFormat;
import java.util.Vector;
import java.*;
import DataObjects.AddWorkinHoursINandOUT;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
//import com.sun.istack.internal.logging.Logger;
 

public class AddNewWorkingHoursOfEmployee {

	private Vector<String> workHours;
	private int status=0;
	private String totalToday;
	private String totalMonth;
	private Date date_out;
	private Time clock_out;
	private Vector<String> totalTodayWorkHours;
//	private Vector<String> totalTodayWorkHours2;
	private Vector<String> totalMonthWorkHours;
//	private Vector<Vector> totalMonthWorkHours;
	private Vector<String> datesHours;
	private int work_hrs_idInt;  
	private AddWorkinHoursINandOUT InOut;
	public int temp ;
 
	float num;
	float ost;
	long maxResponseTime = 16;
	
	public AddNewWorkingHoursOfEmployee(Vector<String> empHours)
	{
		
		this.workHours=empHours;		 
		this.totalTodayWorkHours = new Vector<String>(); 
		this.totalMonthWorkHours = new Vector<String>();
		this.datesHours=new Vector<String>();
	    this.work_hrs_idInt=0;
	}
	
	
	public void saveNewHours()
	{
		SQLConnection sc=new SQLConnection();
		try {
			
			if(workHours.get(3)==null)    //save in clock
			{
					
			PreparedStatement ps2 = sc.getConnection().prepareStatement("INSERT INTO employee_working_hours set clock_in=?, Eid=?, date=?;");    //("INSERT INTO employee_working_hours set clock_in=?,in_hours=?,in_minuts=?,in_seconds=?, Eid=?, date=?;"); // "UPDATE employee_working_hours set clock_in=?,in_hours=?,in_minuts=?,in_seconds=? WHERE Eid=? and date=?;"); 
			 
			sc.getConnection().setAutoCommit(false);
			ps2.setString(1,(String) workHours.get(2)); 
			ps2.setString(2,(String) workHours.get(0));
			ps2.setString(3,(String) workHours.get(1));
		 
			ps2.executeUpdate();
			sc.getConnection().commit();
			ps2.close();
			status=1;
			sc.getConnection().close(); 
							
	    	}
				else                           //save time out in clock out
				{
					 
				 
					PreparedStatement ps1 = sc.getConnection().prepareStatement("SELECT max(work_hrs_id) FROM employee_working_hours WHERE Eid=?");   //("SELECT work_hrs_id FROM employee_working_hours WHERE Eid=? and date=? and clock_out=null");        //("SELECT MAX(work_hrs_id) MAXid FROM employee_working_hours WHERE Eid=? and date=?;");
				//	sc.getConnection().setAutoCommit(false); // ubrat
					ps1.setString(1,(String) workHours.get(0));
				 
					ResultSet rsps=ps1.executeQuery(); 
					                                  
		   
					while (rsps.next())
					{
						
						work_hrs_idInt=rsps.getInt(1);

						if(work_hrs_idInt==0){
						 
							work_hrs_idInt=0;
						}
									
				
					}
					
					
					 PreparedStatement ps3 = sc.getConnection().prepareStatement("SELECT date, clock_in FROM employee_working_hours ewh Where Eid=? and work_hrs_id=?;");   //("SELECT work_hrs_id FROM employee_working_hours WHERE Eid=? and date=? and clock_out=null");        //("SELECT MAX(work_hrs_id) MAXid FROM employee_working_hours WHERE Eid=? and date=?;");
					 ps3.setString(1,(String) workHours.get(0));
					 ps3.setInt(2, work_hrs_idInt );
					 ResultSet rs3=ps3.executeQuery();
					 
					 ResultSetMetaData rsmd3 = rs3.getMetaData();
					 Date date_in = null;
					 Time clock_in = null; 
				 
					  
								while(rs3.next())
					        {  
							 
							 for(int col3 = 1; col3 <= rsmd3.getColumnCount(); col3++) 
					         	{                      	                
							    						
								 int type3 = rsmd3.getColumnType(col3);
					                switch(type3)
					                {
					                case Types.DATE :
					                	 date_in = rs3.getDate(col3);
					                	 
				                        break;   
				                        
					                case Types.TIME :      
							           	 
							         	clock_in = rs3.getTime(col3);
							         	
							        
							          break;  
					                }
					                
					            }
					        }
					                
								 ps3.close();
								 rs3.close();	
					
							  
					PreparedStatement ps2 = sc.getConnection().prepareStatement("UPDATE employee_working_hours set clock_out=?,date_out=? WHERE Eid=? and work_hrs_id=?");     // and work_hrs_id=?");             //("UPDATE employee_working_hours set clock_out=?,out_hours=?,out_minuts=?,out_seconds=? WHERE Eid=? and date=? and work_hrs_id=?");  
					sc.getConnection().setAutoCommit(false);   // NUJEN ESHE TNAI DLIA poslednego vhoda . opredelit posledniuiu knisu  wrk id       [solved]
					 
					if(workHours.get(2).equals("NULL")){
						//Time time = null;
					 
						ps2.setNull(1, java.sql.Types.TIME);   //java.sql.Types.TIME);  setNull(2, java.sql.Types.DATE);
					}
					else {ps2.setString(1,(String) workHours.get(2));}
				 
					ps2.setString(3,(String) workHours.get(0)); 
					ps2.setString(2,(String) workHours.get(1));
					
					ps2.setInt(4, work_hrs_idInt);                  // if user enter and exit several times in the same day 
					
					ps2.executeUpdate();
					sc.getConnection().commit();
			 
								    sc.getConnection().close();
									ps2.close();
									status=1;
									 sc.getConnection().close();
									 
									 
				}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();
			
			
		}
		}
	
	
	
	//------------
	public void clocksAndHours()                                   // proverka esli za odin raz bilo sdelano bolshe chasov chem 12
	{   
		 
		SQLConnection sc=new SQLConnection();
		
		try {

	 PreparedStatement ps4 = sc.getConnection().prepareStatement("SELECT date, clock_in, date_out, clock_out FROM employee_working_hours ewh Where Eid=? and work_hrs_id=?;");   //("SELECT work_hrs_id FROM employee_working_hours WHERE Eid=? and date=? and clock_out=null");        //("SELECT MAX(work_hrs_id) MAXid FROM employee_working_hours WHERE Eid=? and date=?;");
	 ps4.setString(1,(String) workHours.get(0));
	 ps4.setInt(2, work_hrs_idInt );
	 ResultSet rs4=ps4.executeQuery();
	 
	 ResultSetMetaData rsmd4 = rs4.getMetaData();
	  
				while(rs4.next())
	        {  
			 
			 for(int col4 = 1; col4 <= rsmd4.getColumnCount(); col4++) 
	         	{                      	                
			    						
				 int type4 = rsmd4.getColumnType(col4);
	                switch(type4)
	                {
	                case Types.DATE :
	                
	                    datesHours.add(rs4.getDate(col4).toString());
	                
                       break;   
                       
	                case Types.TIME :      
	                 
	                    if(rs4.getTime(col4)!=null)
                        { 
                    
	                    	datesHours.add(rs4.getTime(col4).toString());
                   	 
                   	    }
                   	 else  datesHours.add("empty");       // לא בוצע יציאה
	                    
			          break;  
	                }
	                
	            }
	        }
			 
				 ps4.close();
				 rs4.close();	
			 
			 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
		 
			e.printStackTrace();
		}	
		
	}
	
	 
	
	public void calculateHours()
	{
		 
		SQLConnection sc1=new SQLConnection();
		
		try {
			
			   PreparedStatement ps2 = sc1.getConnection().prepareStatement("SELECT max(work_hrs_id) FROM employee_working_hours WHERE Eid=?");   //("SELECT work_hrs_id FROM employee_working_hours WHERE Eid=? and date=? and clock_out=null");        //("SELECT MAX(work_hrs_id) MAXid FROM employee_working_hours WHERE Eid=? and date=?;");
			//	sc.getConnection().setAutoCommit(false); // ubrat
				ps2.setString(1,(String) workHours.get(0));
			 
				ResultSet rsps=ps2.executeQuery(); 
				int work_hrs_idInt=0;                  
	   
				while (rsps.next())
				{
					
					work_hrs_idInt=rsps.getInt(1);

					if(work_hrs_idInt==0){
					 
						work_hrs_idInt=0;
					}
								
			
				}
				 
				 PreparedStatement ps3 = sc1.getConnection().prepareStatement("SELECT date FROM employee_working_hours ewh Where Eid=? and work_hrs_id=?;");   //("SELECT work_hrs_id FROM employee_working_hours WHERE Eid=? and date=? and clock_out=null");        //("SELECT MAX(work_hrs_id) MAXid FROM employee_working_hours WHERE Eid=? and date=?;");
				 ps3.setString(1,(String) workHours.get(0));
				 ps3.setInt(2, work_hrs_idInt );
				 ResultSet rs3=ps3.executeQuery();
				 
				 ResultSetMetaData rsmd3 = rs3.getMetaData();
				 String date_in = null;
				
							while(rs3.next())
				        {  
						 
						 for(int col3 = 1; col3 <= rsmd3.getColumnCount(); col3++) 
				         	{                      	                
						    						
							 int type3 = rsmd3.getColumnType(col3);
				                switch(type3)
				                {
				                case Types.DATE :
				                	date_in = rs3.getDate(col3).toString();
				                	 
			                        break;   
			                        
				               
				                }
				                
				            }
				        }
				                
							 ps3.close();
							 rs3.close();	
				 
			PreparedStatement ps1 = sc1.getConnection().prepareStatement("SELECT clock_in,clock_out FROM employee_working_hours ewh Where Eid=? and date=?;");   //("SELECT out_hours,in_hours,out_minuts,in_minuts FROM employee_working_hours ewh Where Eid=? and date=?;");    // dobavit = work_id
			ps1.setString(1,(String) workHours.get(0));
			ps1.setString(2, date_in );                      //   Date of last login, 
			
			ResultSet rs1=ps1.executeQuery();
			ResultSetMetaData rsmd = rs1.getMetaData();
			
			//totalTodayWorkHours = new Vector<String>();
					while(rs1.next())
		        {  
				 
				 for(int col = 1; col <= rsmd.getColumnCount(); col++) 
		         	{                      	                
				    						
					 int type = rsmd.getColumnType(col);
		                switch(type)
		                {
		                
		                    case Types.INTEGER :
		                    	totalTodayWorkHours.add(new Integer(rs1.getInt(col)).toString());
		                        break;
		                        
		                    case Types.VARCHAR :
		                    	totalTodayWorkHours.add(rs1.getString(col));
		                        break;
		                    case Types.DATE :
		                    	totalTodayWorkHours.add(rs1.getDate(col).toString());
		                    	
		                        break;   
		                        
		                    case Types.TIME :      
			                   	 
		                    	 if(rs1.getTime(col)!=null)
		                         { 
		                     
		                    		 totalTodayWorkHours.add(rs1.getTime(col).toString());
		                    	 
		                    	 }
		                    	 else  totalTodayWorkHours.add("empty");       // לא בוצע יציאה
		                     break;  
				
				
		                }
		               
		         	}
				
				 
						        }
					 
			 ps1.close();
			 rs1.close();	
			 
			 
			 PreparedStatement ps = sc1.getConnection().prepareStatement("SELECT clock_in, clock_out FROM employee_working_hours ewh Where Eid=? and MONTH(date)=? ;");     // compare by current month 
				
		 
			 
			 
			    ps.setString(1,(String) workHours.get(0));
			  
		 		ps.setString(2,(String) workHours.get(5));
			 	
				ResultSet rs=ps.executeQuery();
				ResultSetMetaData rsmd1 = rs.getMetaData();
				 
				 while(rs.next())
			        {  
					// totalTodayWorkHours2 = new Vector<String>();
					 for(int col = 1; col <= rsmd1.getColumnCount(); col++) 
			         	{                      	                
			               
						 int type = rsmd1.getColumnType(col);
			              switch(type)
			              {
			             
			                    case Types.INTEGER :
			                    	totalMonthWorkHours.add(new Integer(rs.getInt(col)).toString());   //  not in use  
			                        break;
			                        
			                    case Types.VARCHAR :
			                    	totalMonthWorkHours.add(rs.getString(col));             //  not in use
			                        break;
			                    case Types.DATE :
			                    	totalMonthWorkHours.add(rs.getDate(col).toString());      //  not in use
			                        break;   
			                        
			                    case Types.TIME :      
			                    	 			                   	 
			                    	 if(rs.getTime(col)!=null)
			                         { 
			                     
					             	 totalMonthWorkHours.add(rs.getTime(col).toString());
			                    	 
			                    	 }
			                    	 else  totalMonthWorkHours.add("empty");
			                     break;  
					
			               }
			         	}
				//	 totalMonthWorkHours.add(totalTodayWorkHours2);
					
							        }
				 
				 rs.close();	
				 ps.close();
				 
			 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
		 
			e.printStackTrace();
		}	
		
	}
		
	             public Vector<String> getTotalMonthWorkHours() {
		return totalMonthWorkHours;
	}


	             public void setTotalMonthWorkHours(Vector<String> totalMonthWorkHours) {
		this.totalMonthWorkHours = totalMonthWorkHours;
	}


				public Vector<String> getTotalTodayWorkHours() {
		return totalTodayWorkHours;
	}


	            public void setTotalTodayWorkHours(Vector<String> totalWorkHours) {
		this.totalTodayWorkHours = totalWorkHours;
	}


				public int getStatus() {
		               return status;
	              }


	             public void setStatus(int status) {
		              this.status = status;
	              }
	             
	            
	           
	             public Vector<String> getworkHours() {
	         		return workHours;
	         	}
	             
	             public void setworkHours(Vector<String> workHours1) {
	         		this.workHours = workHours1;
	         	} 
	             

	             public Vector<String> getdatesHours() {
	         		return datesHours;
	         	}
	             
	             public void setdatesHours(Vector<String> workHours1) {
	         		this.datesHours = workHours1;
	         	} 
	             
	             
	             
}
